package com.njlime.member.repository.sms;

import com.njlime.core.base.repository.impl.BaseDaoImpl;
import com.njlime.core.helper.PageBean;
import com.njlime.core.utils.DateUtil;
import com.njlime.core.utils.StringUtil;
import com.njlime.member.entiies.TField;
import com.njlime.member.entiies.TMember;
import com.njlime.sys.entities.Unit;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.*;

/**
 * Created by Administrator on 2017-7-18.
 */
@Repository
public class MemberDaoImpl extends BaseDaoImpl<TMember> {

    public List<TMember> getMemberBybirthList(Integer unitId, Date date){
        StringBuilder sb = new StringBuilder();
        List<Object> arrList = new ArrayList<Object>();
        sb.append("select * from t_member t ");
        sb.append("where t.unit_id = ?  and t.birthday = ? ");
        arrList.add(unitId);
        arrList.add(     DateUtil.dateFormat(date,"MM-dd") );

        return findList(sb.toString(), arrList);
    }
    /**
     * 获取用户当前的组
     * @param groupId
     * @param ids
     * @return
     */
    public List<TMember>  selectUserGroupList(int groupId, String ids,int unitId){
        StringBuilder sb = new StringBuilder();
        List<Object> arrList = new ArrayList<Object>();
        sb.append("select * from t_member t ");
        sb.append("where t.unit_id = ?  and t.openid is not null and (  ");
        arrList.add(unitId);
        if(ids.indexOf(",")>0){
            int i = 0;
            for(String id :ids.split(",")){
                if(i >0){
                    sb.append(" or ");
                }
                sb.append(" id = ?"  );
                arrList.add(id);
            }
        }else {
            sb.append(" id = ?"  );
            arrList.add(ids);
        }
        sb.append(" ) ");
        return findList(sb.toString(), arrList);
    }

    /**
     * 获取列表
     *
     * @param pageBean
     * @return
     */
    public PageBean list(PageBean pageBean, TMember member,String startDay,String endDay,String condition,String flag,String priceType,String orderType) {
        StringBuilder sb = new StringBuilder();
        List<Object> arrList = new ArrayList<Object>();
        sb.append("select t.*,u.name group_name from t_member t ");
        sb.append("left join t_member_group u on u.id = t.member_group_id  and t.unit_id=u.unit_id ");
        sb.append("where t.unit_id = ?  ");
        arrList.add(member.getUnit_id());
        if(!StringUtil.isEmpty(member.getName())){
            sb.append("and t.name = ? ");
            arrList.add(member.getName());
        }

        if(!StringUtil.isEmpty(startDay)){
            sb.append("and t.create_time >? ");
            arrList.add(startDay.replace("-",""));
        }
        if(!StringUtil.isEmpty(endDay)){
            sb.append("and t.create_time <= ? ");
            arrList.add( endDay.replace("-","") );
        }

        if(member.getProvince_id()!=null && member.getProvince_id().intValue()!=-1){
            sb.append("  and t.province_id = ? ");
            arrList.add(member.getProvince_id());
        }
        if(member.getCity_id()!=null && member.getCity_id().intValue()!=-1){
            sb.append("  and t.city_id = ? ");
            arrList.add(member.getCity_id());
        }

        if(member.getMember_group_id()!=null && member.getMember_group_id().intValue()>0){
            sb.append("  and t.member_group_id = ? ");
            arrList.add(member.getMember_group_id());
        }
        if(member.getGag()!=null && member.getGag().intValue()!=-1){
            sb.append("  and t.gag = ? ");
            arrList.add(member.getGag());
        }
        if( StringUtils.isNotEmpty(condition)){
                sb.append(" and t.name like ? ");
                arrList.add("%"+condition+"%");
        }
        if(StringUtils.isNotEmpty(priceType)){
            if("1".equals(priceType)){
                sb.append(" and t.order_money >=1 and t.order_money <= 100    ");
            }else  if("2".equals(priceType)){
                sb.append(" and t.order_money > 100 and t.order_money <200    ");
            }else  if("3".equals(priceType)){
                sb.append(" and t.order_money > 200   ");
            }
        }

        if(StringUtils.isNotEmpty(orderType)){
            if("1".equals(orderType)){
                sb.append(" and t.order_count >=1 and t.order_count <= 100    ");
            }else  if("2".equals(orderType)){
                sb.append(" and t.order_count > 100 and t.order_count <200    ");
            }else  if("3".equals(orderType)){
                sb.append(" and t.order_count > 200   ");
            }
        }
   //     dataScopeFilter(UserUtils.getUser().getId(), sb, arrList, "u", "t");
        int num = getListJdbcSize(sb.toString(), arrList);
        pageBean.setTotalCount(num);
        pageBean.setTotalPage(pageBean.getTotalPage());
        if (num > 0) {
            sb.append(" order by t.id desc limit ?,?");
            arrList.add((pageBean.getCurPage() - 1) * pageBean.getPageSize());
            arrList.add(pageBean.getPageSize());
            pageBean.setMapList(getListJdbc(sb.toString(), arrList));
        }
        return pageBean;
    }



    public  PageBean  getReportList(PageBean pb, String provinceId, String startDay, String endDay, String cityId) {
        StringBuilder sb = new StringBuilder();
        List<Object> arrList = new ArrayList<Object>();
        Map<String,Object> map = new HashMap<String,Object>();
        int size =0;
        // 省和市都没选
        if((provinceId==null ||"".equals(provinceId)) && (cityId==null || "".equals(cityId))) {
            sb.append("select * from (select ");
            sb.append(" report_date  ");
            sb.append(" from t_report a");
            sb.append(" WHERE 1=1  ");
            if (StringUtils.isNotEmpty(startDay)) {
                sb.append("  and a.report_date >=? ");
                arrList.add(startDay);
            }
            if (StringUtils.isNotEmpty(endDay)) {
                sb.append("  and a.report_date <=? ");
                arrList.add(endDay);
            }

            //     dataScopeFilter(UserUtils.getUser().getId(), sb, arrList, "u", "t");
            sb.append(" group by report_date  ) t");
            size = getListJdbcSize(sb.toString(), arrList);
            arrList.clear();
            sb.setLength(0);
            sb.append("select ");
            sb.append(" report_date,sum(new_num) new_num,'全部' city_name, '全部' province_name,");
            sb.append(" (SELECT  sum(new_num)  FROM t_report b WHERE ");
            sb.append(" b.report_date = a.report_date ");

            if (StringUtils.isNotEmpty(startDay)) {
                sb.append("  and b.report_date >=? ");
                arrList.add(startDay);
            }
            if (StringUtils.isNotEmpty(endDay)) {
                sb.append("  and b.report_date <=? ");
                arrList.add(endDay);
            }
            sb.append(" ) all_num");
            sb.append(" from t_report a");
            sb.append(" WHERE 1=1  ");
            if (StringUtils.isNotEmpty(startDay)) {
                sb.append("  and a.report_date >=? ");
                arrList.add(startDay);
            }
            if (StringUtils.isNotEmpty(endDay)) {
                sb.append("  and a.report_date <=? ");
                arrList.add(endDay);
            }

            //     dataScopeFilter(UserUtils.getUser().getId(), sb, arrList, "u", "t");
            sb.append(" group by report_date order by a.id desc limit ?,?");
            arrList.add((pb.getCurPage() - 1) * pb.getPageSize());
            arrList.add(pb.getPageSize());
            pb.setTotalCount(size);
        }
        // 只选了省
        else  if((provinceId!=null || !"".equals(provinceId)) && (cityId==null || "".equals(cityId))) {
            sb.append("select * from (select ");
            sb.append(" report_date,major_id ,provinceId ");
            sb.append(" from t_report a");
            sb.append(" WHERE 1=1  ");
            if (StringUtils.isNotEmpty(startDay)) {
                sb.append("  and a.report_date >=? ");
                arrList.add(startDay);
            }
            if (StringUtils.isNotEmpty(endDay)) {
                sb.append("  and a.report_date <=? ");
                arrList.add(endDay);
            }

            sb.append("  and a.province_d =? ");
            arrList.add(provinceId);
            sb.append(" group by report_date,major_id,provinceId  ) t");
            size = getListJdbcSize(sb.toString(), arrList);
            arrList.clear();
            sb.setLength(0);
            sb.append("select ");
            sb.append(" report_date,major_id,d.`name`,sum(new_num) new_num,province_name city_name, ");
            sb.append(" (SELECT  sum(new_num)  FROM t_report b WHERE ");
            sb.append(" b.report_date = a.report_date ");
            sb.append(" and b.major_id=a.major_id  and a.province_name=b.province_name");
            if (StringUtils.isNotEmpty(startDay)) {
                sb.append("  and b.report_date >=? ");
                arrList.add(startDay);
            }
            if (StringUtils.isNotEmpty(endDay)) {
                sb.append("  and b.report_date <=? ");
                arrList.add(endDay);
            }
            sb.append("  and b.provinceId =? ");
            arrList.add(provinceId);
            sb.append(" ) all_num");
            sb.append(" from t_report a");
            sb.append(" left join t_major d on d.id = a.major_id");
            sb.append(" WHERE 1=1  ");
            if (StringUtils.isNotEmpty(startDay)) {
                sb.append("  and a.report_date >=? ");
                arrList.add(startDay);
            }
            if (StringUtils.isNotEmpty(endDay)) {
                sb.append("  and a.report_date <=? ");
                arrList.add(endDay);
            }

            sb.append("  and a.provinceId  =? ");
            arrList.add(provinceId);

            sb.append(" group by report_date,major_id,d.name,province_name order by a.id desc limit ?,?");
            arrList.add((pb.getCurPage() - 1) * pb.getPageSize());
            arrList.add(pb.getPageSize());
            pb.setTotalCount(size);
        }
        else   {
            sb.append("select * from (select ");
            sb.append(" report_date ,provinceId,cityId ");
            sb.append(" from t_report a");
            sb.append(" WHERE 1=1  ");
            if (StringUtils.isNotEmpty(startDay)) {
                sb.append("  and a.report_date >=? ");
                arrList.add(startDay);
            }
            if (StringUtils.isNotEmpty(endDay)) {
                sb.append("  and a.report_date <=? ");
                arrList.add(endDay);
            }

            sb.append("  and a.provinceId =? ");
            arrList.add(provinceId);
            sb.append("  and a.cityId =? ");
            arrList.add(cityId);
            sb.append(" group by report_date,provinceId  ) t");
            size = getListJdbcSize(sb.toString(), arrList);
            arrList.clear();
            sb.setLength(0);
            sb.append("select ");
            sb.append(" report_date,sum(new_num) new_num,city_name  , ");
            sb.append(" (SELECT  sum(new_num)  FROM t_report b WHERE ");
            sb.append(" b.report_date = a.report_date ");
            if (StringUtils.isNotEmpty(startDay)) {
                sb.append("  and b.report_date >=? ");
                arrList.add(startDay);
            }
            if (StringUtils.isNotEmpty(endDay)) {
                sb.append("  and b.report_date <=? ");
                arrList.add(endDay);
            }
            sb.append("  and b.provinceId =? ");
            arrList.add(provinceId);
            sb.append("  and b.cityId =? ");
            arrList.add(cityId);
            sb.append(" ) all_num");
            sb.append(" from t_report a");
            sb.append(" WHERE 1=1  ");
            if (StringUtils.isNotEmpty(startDay)) {
                sb.append("  and a.report_date >=? ");
                arrList.add(startDay);
            }
            if (StringUtils.isNotEmpty(endDay)) {
                sb.append("  and a.report_date <=? ");
                arrList.add(endDay);
            }

            sb.append("  and a.provinceId  =? ");
            arrList.add(provinceId);
            sb.append("  and a.cityId =? ");
            arrList.add(cityId);

            sb.append(" group by report_date,province_name order by a.id desc limit ?,?");
            arrList.add((pb.getCurPage() - 1) * pb.getPageSize());
            arrList.add(pb.getPageSize());
            pb.setTotalCount(size);
        }


        if(size>0){
            pb.setMapList( getListJdbc(sb.toString(), arrList));
        }
        return pb;
    }

    /**
     * 统计人数
     * @param startDate
     */
    public void addReportInfo(String startDate){
        StringBuilder sb = new StringBuilder();
        List<Object> arrList = new ArrayList<Object>();
        sb.append("delete from  t_report     ");
        sb.append(" WHERE   ");
        sb.append("     report_date = ?");
        arrList.add(startDate);
        updateJdbc(sb.toString(), arrList);

        sb.setLength(0);
        arrList = new ArrayList<Object>();
        sb.append(" INSERT INTO t_report (");
        sb.append(" 	report_date,");
        sb.append(" 	province_id,");
        sb.append(" 	province_name,");
        sb.append(" 	city_id,");
        sb.append(" 	city_name,");
        sb.append(" 	new_num,unit_id ");
        sb.append(" ) SELECT");
        sb.append(" 	report_date,");
        sb.append(" 	-1,");
        sb.append(" 	province_id,");
        sb.append(" 	province_name,");
        sb.append(" 	city_id,");
        sb.append(" 	city_name,");
        sb.append(" 	count(DISTINCT user_id),unit_id");
        sb.append(" FROM");
        sb.append(" 	t_report_info t");
        sb.append(" WHERE");
        sb.append(" 	t.report_date = ?");
        sb.append(" GROUP BY");
        sb.append(" 	report_date,");
        sb.append(" 	province_id,");
        sb.append(" 	province_name,");
        sb.append(" 	city_id,");
        sb.append(" 	city_name,unit_id");
        arrList.add(startDate);
        updateJdbc(sb.toString(), arrList);

    }

    /**
     * 更新
     *
     * @param info 会员内容
     * @return
     */
    public int updateStatus(TMember info) {
        StringBuilder sb = new StringBuilder();
        List<Object> arrList = new ArrayList<Object>();
        sb.append("UPDATE t_member set manager_udate_time = now(),manager_id = ?");
        arrList.add(info.getManager_id());
        sb.append(",status = ? ");
        sb.append(" where id= ?  and unit_id = ?");

        arrList.add(info.getStatus());
        arrList.add(info.getId());
        arrList.add(info.getUnit_id());
        return updateJdbc(sb.toString(), arrList);
    }

    /**
     * 更新
     *
     * @param id 会员id
     * @param groupId 分组id
     *
     * @return
     */
    public int editMemberGroup(int id, int groupId,int unitId) {
        StringBuilder sb = new StringBuilder();
        List<Object> arrList = new ArrayList<Object>();
        sb.append("UPDATE t_member set member_group_id =  ?");
        arrList.add(groupId);

        sb.append(" where id= ?  and unit_id = ?");

        arrList.add(id);
        arrList.add(unitId);
        return updateJdbc(sb.toString(), arrList);
    }


    public int updateGag(TMember info){
        StringBuilder sb = new StringBuilder();
        List<Object> arrList = new ArrayList<Object>();
        sb.append("UPDATE t_member set gag =  ?");
        arrList.add(info.getGag());

        sb.append(" where id= ?  and unit_id = ?");

        arrList.add(info.getId());
        arrList.add(info.getUnit_id());
        return updateJdbc(sb.toString(), arrList);
    }

    /**
     * 欢迎页面统计
     * @param unit
     * @return
     */
    public Map<String,Object> welcomeReport(Unit unit){
        StringBuilder sb = new StringBuilder();
        List<Object> arrList = new ArrayList<Object>();
        sb.append("select IFNULL(count(1),0) memberNum from t_member t ");
        sb.append("join t_unit u on u.id = t.unit_id " );
        sb.append("where (u.id = ? or u.parent_ids like concat(?,'%')) ");
        arrList.add(unit.getId());
        arrList.add(unit.getParent_ids() + unit.getId() + ",");
        return getListJdbc(sb.toString(),arrList).get(0);
    }


    public List<Map<String,Object>> memberReport(Unit unit, Date start, Date end){
        StringBuilder sb = new StringBuilder();
        List<Object> arrList = new ArrayList<Object>();
        sb.append("select sum(new_num) sub_num, report_date from t_member_report t ");
        sb.append("join t_unit u on u.id = t.unit_id ");
        sb.append("where (u.id = ? or u.parent_ids like concat(?,'%')) and t.report_date >= ?  and t.report_date <= ? ");
        sb.append("group by report_date");
        arrList.add(unit.getId());
        arrList.add(unit.getParent_ids() + unit.getId() + ",");
        arrList.add(start);
        arrList.add(end);
        return getListJdbc(sb.toString(),arrList);
    }

    /**
     * 统计人数
     */
    public void userReport(){
        StringBuilder sb = new StringBuilder();
        List<Object> arrList = new ArrayList<Object>();
        String date = DateUtil.dateFormat(new Date(), "yyyy-MM-dd");
        //清理已有数据
        sb.append("delete from  t_member_report where report_date = ?");
        arrList.add(date);
        updateJdbc(sb.toString(), arrList);
        //进行统计
        sb.setLength(0);
        arrList = new ArrayList<Object>();
        sb.append("insert into t_member_report (unit_id,report_date,province_id,province_name,city_id,city_name,new_num,all_num,vip_num,unvip_num,active_num)");
        sb.append("select t.unit_id,?,province_id,IFNULL(province_name, '其他') province_name,city_id,IFNULL(city_name, '其他') city_name,");
        sb.append("(select count(1) from t_member t1 where IFNULL(t1.province_name, '') = IFNULL(t.province_name, '') and IFNULL(t1.city_name, '') = IFNULL(t.city_name, '')" +
                "and t1.unit_id = t.unit_id and t1.create_time >= ?) num,");
        sb.append("(select ifnull(sum(1),0) from t_member t1 where IFNULL(t1.province_name, '') = IFNULL(t.province_name, '') and IFNULL(t1.city_name, '') = IFNULL(t.city_name, '')" +
                " and t1.unit_id = t.unit_id) all_num,");
        sb.append("(select ifnull(sum(1),0) from t_member t1 where IFNULL(t1.province_name, '') = IFNULL(t.province_name, '') and IFNULL(t1.city_name, '') = IFNULL(t.city_name, '')" +
                " and t1.unit_id = t.unit_id and t1.order_count > 0) vip_num,");
        sb.append("(select ifnull(sum(1),0) from t_member t1 where IFNULL(t1.province_name, '') = IFNULL(t.province_name, '') and IFNULL(t1.city_name, '') = IFNULL(t.city_name, '')" +
                " and t1.unit_id = t.unit_id and t1.order_count <= 0) unvip_num,");
        sb.append("(select ifnull(sum(1),0) from t_member t1 where IFNULL(t1.province_name, '') = IFNULL(t.province_name, '') and IFNULL(t1.city_name, '') = IFNULL(t.city_name, '')" +
                " and update_time >= ? and update_time < ? and t1.unit_id = t.unit_id) active_num ");
        sb.append("from t_member t ");
        //sb.append("where t.create_time >= ? ");
        sb.append("group by t.province_id,t.city_id,t.unit_id");
        arrList.add(date);
        arrList.add(date + " 00:00:00");
        arrList.add(date + " 00:00:00");
        arrList.add(date + " 23:59:59");
        updateJdbc(sb.toString(), arrList);
    }

    /**
     * 统计
     * @param pageBean
     * @param startTime
     * @param endTime
     * @param province_id
     * @param city_id
     * @return
     */
    public PageBean report(PageBean pageBean, Unit unit, Date startTime, Date endTime, Integer province_id, Integer city_id){
        StringBuilder sb = new StringBuilder();
        List<Object> arrList = new ArrayList<Object>();
        sb.append("select t.* from t_member_report t ");
        sb.append("join t_unit u on u.id = t.unit_id ");
        sb.append("where (u.id = ? or u.parent_ids like concat(?,'%')) ");
        arrList.add(unit.getId());
        arrList.add(unit.getParent_ids() + unit.getId() + ",");
        if(startTime != null){
            sb.append("and DATE_FORMAT(t.report_date, '%Y-%m-%d') >= DATE_FORMAT(?, '%Y-%m-%d')");
            arrList.add(startTime);
        }
        if(endTime != null){
            sb.append("and DATE_FORMAT(t.report_date, '%Y-%m-%d') <= DATE_FORMAT(?, '%Y-%m-%d')");
            arrList.add(endTime);
        }
        if(province_id != null && province_id != 0){
            sb.append("and t.province_id = ? ");
            arrList.add(province_id);
        }
        if(city_id != null && city_id != 0){
            sb.append("and t.city_id = ? ");
            arrList.add(city_id);
        }
        int num = getListJdbcSize(sb.toString(), arrList);
        pageBean.setTotalCount(num);
        pageBean.setTotalPage(pageBean.getTotalPage());
        if (num > 0) {
            sb.append(" order by t.report_date desc limit ?,?");
            arrList.add((pageBean.getCurPage() - 1) * pageBean.getPageSize());
            arrList.add(pageBean.getPageSize());
            pageBean.setMapList(getListJdbc(sb.toString(), arrList));
        }
        return pageBean;
    }

    /**
     * 合计
     * @param startTime
     * @param endTime
     * @param province_id
     * @param city_id
     * @return
     */
    public Map<String, Object> sumReport(Unit unit, Date startTime, Date endTime, Integer province_id, Integer city_id){
        StringBuilder sb = new StringBuilder();
        List<Object> arrList = new ArrayList<Object>();
        sb.append("select '合计' report_date, sum(new_num) new_num from t_member_report t ");
        sb.append("join t_unit u on u.id = t.unit_id ");
        sb.append("where (u.id = ? or u.parent_ids like concat(?,'%')) ");
        arrList.add(unit.getId());
        arrList.add(unit.getParent_ids() + unit.getId() + ",");
        if(startTime != null){
            sb.append("and DATE_FORMAT(t.report_date, '%Y-%m-%d') >= DATE_FORMAT(?, '%Y-%m-%d')");
            arrList.add(startTime);
        }
        if(endTime != null){
            sb.append("and DATE_FORMAT(t.report_date, '%Y-%m-%d') <= DATE_FORMAT(?, '%Y-%m-%d')");
            arrList.add(endTime);
        }
        if(province_id != null){
            sb.append("and t.province_id = ? ");
            arrList.add(province_id);
        }
        if(city_id != null){
            sb.append("and t.city_id = ? ");
            arrList.add(city_id);
        }
        List<Map<String,Object>> list = getListJdbc(sb.toString(),arrList);
        if(list != null && list.size() > 0){
            return list.get(0);
        }
        return null;
    }

}
